-- =====================================================
-- Author:        Mario Moreno
-- Create date: 14-Jul-2008
-- Description:    
-- =====================================================
CREATE FUNCTION [dbo].[SearchForSuperProduct](@SuperProductID INT, @IsSuperProduct BIT, @SearchString varchar(200), @MatchType int)
RETURNS INT
AS

BEGIN
    IF(@IsSuperProduct = 1)
        BEGIN
            DECLARE @i1 INT;
            DECLARE @i2 INT;
            DECLARE @Word VARCHAR(100);
            DECLARE @Words TABLE (Word VARCHAR(100) NOT NULL);
            DECLARE @WordCount AS INTEGER;
            DECLARE @SuperProductAttribute TABLE (
            Title            VARCHAR(128)
            )

            INSERT INTO @SuperProductAttribute
                SELECT DISTINCT av.Title
                FROM OrderSuperProductAttributeLookup AS a
                INNER JOIN OrderSuperProductAttribute AS b ON a.OrderSuperProductAttributeID = b.OrderSuperProductAttributeID,
OrderSuperProductAttributeValue AS av INNER JOIN
                OrderSuperProductAttributeLookup AS al ON av.OrderSuperProductAttributeID = al.OrderSuperProductAttributeID INNER JOIN
                OrderSuperProductChildProduct AS cp ON al.OrderProductID = cp.OrderProductID INNER JOIN
                OrderSuperProductChildProductAttribute AS cpa ON av.OrderSuperProductAttributeValueID = cpa.OrderSuperProductAttributeValueID AND
                cp.OrderSuperProductChildProductID = cpa.OrderSuperProductChildProductID
                INNER JOIN Product_Inventory pinv ON pinv.PRODUCT_CODE COLLATE database_default = cp.ProductCode COLLATE database_default
                WHERE a.OrderProductID = @SuperProductID AND
                  al.OrderProductID = @SuperProductID
                AND (pinv.QUANTITY_AVAILABLE > 0)
                AND av.OrderSuperProductAttributeID = b.OrderSuperProductAttributeID

            IF (@MatchType != 2)
                BEGIN
                    SET @SearchString = ' ' + @SearchString  + ' ';
                    SET @i1 = 1;
                    WHILE (@i1 != 0)
                        BEGIN
                            SET @i2=charindex(' ', @SearchString, @i1+1)
                            IF (@i2 != 0)
                                BEGIN
                                    SET @Word = rtrim(ltrim(substring(@SearchString, @i1+1, @i2-@i1)))
                                    IF @Word != '' INSERT INTO @Words SELECT @Word
                                END

                            SET @i1 = @i2
                        END
                END
            ELSE
                INSERT INTO @Words SELECT LTRIM(RTRIM(@SearchString))

        -- Get the total # of words:

            SET @WordCount = (SELECT COUNT(*) FROM @Words)

            DECLARE @result INT
            SELECT @result = COUNT(*) FROM
                (SELECT a.MatchPct, T.*
                    FROM @SuperProductAttribute T
                    INNER JOIN
                    (
                        SELECT
                        T.Title, Count(*)  * 1.0 / @WordCount AS MatchPct
                        FROM
                        @SuperProductAttribute T
                        INNER JOIN
                        @Words W ON ' ' + T.Title + ' ' LIKE '%[^a-z]' + Word + '[^a-z]%'
                        GROUP BY
                        T.Title
                    ) a ON T.Title = a.Title
                    WHERE
                    MatchPct = 1 OR @MatchType <>1
                ) AS res
            IF (@result = 0)
                RETURN 0
            ELSE
                RETURN 1
        END
    RETURN 0
END

GO